package com.liuya.db.orm.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import com.liuya.common.StringUtil;
import com.liuya.db.ColumnAdapter;
import com.liuya.db.DBLevelException;
import com.liuya.db.DBPower;
import com.liuya.db.ObjectNewer;
import com.liuya.db.util.DBUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.liuya.db.orm.Column;
import com.liuya.db.orm.GroupPart;
import com.liuya.db.orm.OrderPart;
import com.liuya.db.orm.SelectCondition;
import com.liuya.db.orm.SelectConditionUtil;
import com.liuya.db.orm.Table;
import com.liuya.db.orm.TableSelector;
import com.liuya.db.orm.WhereElement;
import com.liuya.db.orm.WhereElementUtil;

/**
 * WARNING!!!! We will deprecate it later.
 */
public class TableSelectorImpl implements TableSelector {
	private ObjectNewer objectNewer;
	private String selectByIdSql;
	private String selectByUniqueSql;
	private String selectSqlWithoutWherePart;
	private String selectCountByIdSql;
	private String selectCountByUniqueSql;

	private Table table;

	private static Log logger = LogFactory.getLog(TableSelectorImpl.class);

	public Table getTable() {
		return table;
	}

	public void setTable(Table table) {
		this.table = table;

		SelectCondition byIdCdtn = SelectConditionUtil.simplyConnectColumns(table.getIdColumns());
		SelectCondition byUniqueCdtn = SelectConditionUtil.simplyConnectColumns(table.getUniqueColumns());

		selectSqlWithoutWherePart = DBUtil.selectSql(table.getName(), table.getColumnNames());

		if (byIdCdtn != null) {
			String sql = WhereElementUtil.toSql(byIdCdtn);
			selectByIdSql = selectSqlWithoutWherePart + sql;
			selectCountByIdSql = "select count(1) from " + table.getName() + sql;
		}

		if (byUniqueCdtn != null) {
			String sql = WhereElementUtil.toSql(byUniqueCdtn);
			selectByUniqueSql = selectSqlWithoutWherePart + sql;
			selectCountByUniqueSql = "select count(1) from " + table.getName() + sql;
		}
	}

	public TableSelectorImpl() {
	}

	public void setObjectNewer(ObjectNewer objectNewer) {
		this.objectNewer = objectNewer;
	}

	public Collection select(SelectCondition cdtn, Object o) throws DBLevelException {
		Connection conn = null;
		try {
			conn = DBPower.getConnectionByTableId(table.getId());
			return select(conn, cdtn, o);
		} finally {
			DBUtil.close(conn);
		}
	}

	public Object selectByIdColumns(Object o) throws DBLevelException {
		Connection conn = null;
		try {
			conn = DBPower.getConnectionByTableId(table.getId());
			return selectByIdColumns(conn, o);
		} finally {
			DBUtil.close(conn);
		}
	}

	public Object selectByUniqueColumns(Object o) throws DBLevelException {
		Connection conn = null;
		try {
			conn = DBPower.getConnectionByTableId(table.getId());
			return selectByUniqueColumns(conn, o);
		} finally {
			DBUtil.close(conn);
		}
	}

	public Collection select(Connection conn, SelectCondition cdtn, Object o) throws DBLevelException {
		String selectSql = selectSqlWithoutWherePart + WhereElementUtil.toSql(cdtn);
		return select(conn, selectSql, cdtn, o);
	}

	public Collection selectByPage(SelectCondition cdtn, Object o, int fromIndex, int pageSize) throws DBLevelException {
		Connection conn = null;
		try {
			conn = DBPower.getConnectionByTableId(table.getId());
			return selectByPage(conn, cdtn, o, fromIndex, pageSize);
		} finally {
			DBUtil.close(conn);
		}
	}

	public Collection selectByPage(Connection conn, SelectCondition cdtn, Object o, int fromIndex, int pageSize) throws DBLevelException {
		String selectSql = "";
		String dbName;
		try {
			dbName = conn.getMetaData().getDatabaseProductName();
			dbName = dbName.toUpperCase();
		} catch (SQLException e) {
			logger.error("", e);
			throw new DBLevelException(e);
		}

		if (dbName.indexOf("MYSQL") != -1) {
			// SQL:select .. from .. order by .. limit ?,?
			selectSql = selectSqlWithoutWherePart + WhereElementUtil.toSql(cdtn) + " limit " + (fromIndex - 1) + "," + pageSize;
		} else if (dbName.indexOf("ORACLE") != -1) {
			StringBuffer buff = new StringBuffer();
			buff.append("SELECT ");
			StringUtil.append(buff, table.getColumnNames());
			buff.append(" FROM (");
			buff.append("select *, rownum as __rn from (").append(selectSqlWithoutWherePart).append(WhereElementUtil.toSql(cdtn)).append(") where __rn<=" + (fromIndex + pageSize - 1)).append(") where __rn>=" + fromIndex);
			selectSql = buff.toString();
		} else if (dbName.indexOf("DB2") != -1) {
			// SQL LOOKS LIKE
			// select * from (
			// SELECT rownumber()
			// over(ORDER BY SCORE DESC)
			// as row_, * FROM STUDENT ORDER BY SCORE DESC )
			// as temp_ where row_ between 11 and 20

			StringBuffer buff = new StringBuffer();
			buff.append("SELECT ");
			StringUtil.append(buff, table.getColumnNames());
			buff.append(" FROM (");
			buff.append("SELECT ");
			StringUtil.append(buff, table.getColumnNames());
			buff.append(",rownumber() over(");
			append(buff, cdtn.getOrderPart());
			buff.append(") as __rn FROM ").append(table.getName());
			append(buff, cdtn.getOrderPart());
			append(buff, cdtn.getGroupPart());
			buff.append(") as temp_ where __rn between ").append(fromIndex).append(" and ").append(fromIndex + pageSize - 1);

			selectSql = buff.toString();
		}

		if (logger.isDebugEnabled()) {
			logger.debug(selectSql);
		}

		if (dbName.indexOf("MYSQL") != -1 || dbName.indexOf("ORACLE") != -1 || dbName.indexOf("DB2") != -1) {
			return select(conn, selectSql, cdtn, o);
		} else {
			return select(conn, selectSql, cdtn, o, fromIndex, pageSize);
		}

	}

	private static void append(StringBuffer buff, GroupPart groupPart) {
		if (groupPart == null || groupPart.getColumnNames().length == 0)
			return;

		String[] columnNames = groupPart.getColumnNames();

		buff.append(" group by ");
		for (int i = 0; i < columnNames.length; i++) {
			if (i != 0) {
				buff.append(",");
			}
			buff.append(columnNames[i]);
		}
	}

	private static void append(StringBuffer buff, OrderPart orderPart) {
		if (orderPart == null || orderPart.getColumnNames().length == 0)
			return;

		String[] columnNames = orderPart.getColumnNames();
		String[] orderTypes = orderPart.getOrderTypes();

		buff.append(" ORDER BY ");
		for (int i = 0; i < columnNames.length; i++) {
			if (i != 0) {
				buff.append(",");
			}
			buff.append(columnNames[i]);
			if (orderTypes != null && "DESC".equalsIgnoreCase(orderTypes[i])) {
				buff.append(" DESC");
			}
		}
	}

	public Object selectByIdColumns(Connection conn, Object o) throws DBLevelException {
		Collection collection = select(conn, selectByIdSql, table.getIdColumns(), o);
		if (collection.size() > 0) {
			return collection.iterator().next();
		} else {
			return null;
		}
	}

	public Object selectByUniqueColumns(Connection conn, Object o) throws DBLevelException {
		Collection collection = select(conn, selectByUniqueSql, table.getUniqueColumns(), o);
		if (collection.size() > 0) {
			return collection.iterator().next();
		} else {
			return null;
		}
	}

	private Collection select(Connection conn, String pstmtSql, SelectCondition cdtn, Object o) throws DBLevelException {
		List selectByColumns = new LinkedList();
		List hints = new LinkedList();
		WhereElement whereElement = null;
		if (cdtn != null) {
			whereElement = cdtn.getWhereElement();
		}
		WhereElementUtil.extractColumns(whereElement, selectByColumns, o, hints);

		return select(conn, pstmtSql, selectByColumns, hints);
	}

	private Collection select(Connection conn, String pstmtSql, SelectCondition cdtn, Object o, int startIndex, int pageSize) throws DBLevelException {
		List selectByColumns = new LinkedList();
		List hints = new LinkedList();
		WhereElementUtil.extractColumns(cdtn == null ? null : cdtn.getWhereElement(), selectByColumns, o, hints);
		return select(conn, pstmtSql, selectByColumns, hints, startIndex, pageSize);
	}

	private Collection select(Connection conn, String pstmtSql, List selectByColumns, List hints) throws DBLevelException {
		List result = new LinkedList();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(pstmtSql);
			if (selectByColumns != null) {
				Iterator columnIter = selectByColumns.iterator();
				Iterator hintsIter = hints.iterator();
				int i = 1;
				while (columnIter.hasNext()) {
					Column column = (Column) columnIter.next();
					ColumnAdapter adapter = column.getAdapter();
					adapter.setPreparedStatement(pstmt, i, hintsIter.next());
					i++;
				}
			}

			rs = pstmt.executeQuery();
			while (rs.next()) {
				Object obj = objectNewer.newObject();
				Column[] columns = table.getColumns();
				for (int i = 0; i < columns.length; i++) {
					Column column = columns[i];
					column.getAdapter().readResultSet(rs, i + 1, obj);
				}
				result.add(obj);
			}
		} catch (SQLException e) {
			logger.error("", e);
			throw new DBLevelException(e);
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pstmt);
		}
		return result;
	}

	private Collection select(Connection conn, String pstmtSql, List selectByColumns, List hints, int startIndex, int pageSize) throws DBLevelException {
		List result = new LinkedList();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(pstmtSql);
			if (selectByColumns != null) {
				Iterator columnIter = selectByColumns.iterator();
				Iterator hintsIter = hints.iterator();
				int i = 1;
				while (columnIter.hasNext()) {
					Column column = (Column) columnIter.next();
					ColumnAdapter adapter = column.getAdapter();
					adapter.setPreparedStatement(pstmt, i, hintsIter.next());
					i++;
				}
			}

			rs = pstmt.executeQuery();

			int index = 1;
			while (index < startIndex && rs.next()) {
				index++;
			}

			int readSize = 0;
			while (rs.next() && readSize < pageSize) {
				Object obj = objectNewer.newObject();
				Column[] columns = table.getColumns();
				for (int i = 0; i < columns.length; i++) {
					Column column = columns[i];
					column.getAdapter().readResultSet(rs, i + 1, obj);
				}
				result.add(obj);
				readSize++;
			}
		} catch (SQLException e) {
			logger.error("", e);
			throw new DBLevelException(e);
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pstmt);
		}
		return result;
	}

	private Collection select(Connection conn, String pstmtSql, Column[] selectByColumns, Object o) throws DBLevelException {
		List result = new LinkedList();

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(pstmtSql);

			if (selectByColumns != null) {
				for (int i = 0; i < selectByColumns.length; i++) {
					ColumnAdapter adapter = selectByColumns[i].getAdapter();
					adapter.setPreparedStatement(pstmt, i + 1, o);
				}
			}

			rs = pstmt.executeQuery();

			while (rs.next()) {
				Object obj = objectNewer.newObject();
				Column[] columns = table.getColumns();
				for (int i = 0; i < columns.length; i++) {
					Column column = columns[i];
					column.getAdapter().readResultSet(rs, i + 1, obj);
				}
				result.add(obj);
			}
		} catch (SQLException e) {
			logger.error("", e);
			throw new DBLevelException(e);
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pstmt);
		}

		return result;
	}

	public int selectCount(SelectCondition cdtn, Object o) {
		Connection conn = null;
		try {
			conn = DBPower.getConnectionByTableId(table.getId());
			return selectCount(conn, cdtn, o);
		} finally {
			DBUtil.close(conn);
		}
	}

	public int selectCount(Connection conn, SelectCondition cdtn, Object o) {
		String sql = "select count(1) from " + table.getName() + WhereElementUtil.toSql(cdtn);
		return selectCount(conn, sql, cdtn, o);
	}

	public boolean isExistByIdColumns(Object o) {
		Connection conn = null;
		try {
			conn = DBPower.getConnectionByTableId(table.getId());
			return isExistByIdColumns(conn, o);
		} finally {
			DBUtil.close(conn);
		}
	}

	public boolean isExistByUniqueColumns(Object o) {
		Connection conn = null;
		try {
			conn = DBPower.getConnectionByTableId(table.getId());
			return isExistByUniqueColumns(conn, o);
		} finally {
			DBUtil.close(conn);
		}
	}

	public boolean isExistByUniqueColumns(Connection conn, Object o) {
		int selectCount = selectCount(conn, selectCountByUniqueSql, table.getUniqueColumns(), o);
		return selectCount > 0 ? true : false;
	}

	public boolean isExistByIdColumns(Connection conn, Object o) {
		int selectCount = selectCount(conn, selectCountByIdSql, table.getIdColumns(), o);
		return selectCount > 0 ? true : false;
	}

	private int selectCount(Connection conn, String sql, SelectCondition cdtn, Object o) throws DBLevelException {
		List selectByColumns = new LinkedList();
		List hints = new LinkedList();
		WhereElementUtil.extractColumns(cdtn == null ? null : cdtn.getWhereElement(), selectByColumns, o, hints);

		return selectCount(conn, sql, selectByColumns, hints);
	}

	private int selectCount(Connection conn, String sql, List selectByColumns, List hints) throws DBLevelException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);

			if (selectByColumns != null) {
				Iterator columnIter = selectByColumns.iterator();
				Iterator hintsIter = hints.iterator();
				int i = 1;
				while (columnIter.hasNext()) {
					Column column = (Column) columnIter.next();
					ColumnAdapter adapter = column.getAdapter();
					adapter.setPreparedStatement(pstmt, i, hintsIter.next());
					i++;
				}
			}

			rs = pstmt.executeQuery();

			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return 0;
			}
		} catch (SQLException e) {
			logger.error("", e);
			throw new DBLevelException(e);
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pstmt);
		}
	}

	private int selectCount(Connection conn, String sql, Column[] selectByColumns, Object o) throws DBLevelException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);

			if (selectByColumns != null) {
				for (int i = 0; i < selectByColumns.length; i++) {
					ColumnAdapter adapter = selectByColumns[i].getAdapter();
					adapter.setPreparedStatement(pstmt, i + 1, o);
				}
			}

			rs = pstmt.executeQuery();

			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return 0;
			}
		} catch (SQLException e) {
			logger.error("", e);
			throw new DBLevelException(e);
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pstmt);
		}
	}
}
